/*
 * CriteriaBean.java
 *
 * $Id: CriteriaBean.java,v 1.3 2009-06-18 10:38:11 juancarlos Exp $
 *
 * Autor: Mario García García - Imagos 2007
 */
package org.ceteca.explica.server.util.db;

import java.util.ArrayList;

import org.hibernate.Criteria;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Restrictions;

/**
 * This bean class holds the data belonging to a query criteria for the WHERE
 * statement in a generic SQL query performed in the database.
 */
public class CriteriaBean {
	/**
	 * Field name referenced by the query criteria.
	 */
	private String fieldId;
	/**
	 * List with the values for the query criteria. We need a list since the
	 * criteria operator might be binary (as for example the BETWEEN operator)
	 * or even need more than two values (as for example the IN operator).
	 */
	private ArrayList lFieldValues;
	/**
	 * Criteria operator.
	 */
	private String fieldOperator;
	/**
	 * Logic operator to append the criteria with the rest of criteria that take
	 * part in the WHERE statement of the SQL query. It shows how this criteria
	 * will be appended to the WHERE statement (OR | AND)
	 */
	private String fieldLogicOperator;
	/**
	 * Prefix with the number of opening brackets appended before the criteria
	 * to keep the correct precedence in the WHERE statement.
	 */
	private String fieldBracketsPrefix;
	/**
	 * Suffix with the number of closing brackets appended after the criteria to
	 * keep the correct precedence in the WHERE statement.
	 */
	private String fieldBracketsSuffix;
	/**
	 * It shows if the field corresponding to the criteria must be normalized or
	 * not.
	 */
	private boolean fieldNormalized;

	public CriteriaBean() {
	}

	public CriteriaBean(String fieldId, ArrayList lFieldValues,
			String fieldOperator, String fieldLogicOperator,
			String fieldBracketsPrefix, String fieldBracketsSuffix) {
		this.fieldId = fieldId;
		this.lFieldValues = lFieldValues;
		this.fieldOperator = fieldOperator;
		this.fieldLogicOperator = fieldLogicOperator;
		this.fieldBracketsPrefix = fieldBracketsPrefix;
		this.fieldBracketsSuffix = fieldBracketsSuffix;
	}

	public CriteriaBean(String fieldId, Object fieldValue,
			String fieldOperator, String fieldLogicOperator,
			String fieldBracketsPrefix, String fieldBracketsSuffix) {
		this.fieldId = fieldId;
		this.lFieldValues = new ArrayList();
		this.lFieldValues.add(fieldValue);
		this.fieldOperator = fieldOperator;
		this.fieldLogicOperator = fieldLogicOperator;
		this.fieldBracketsPrefix = fieldBracketsPrefix;
		this.fieldBracketsSuffix = fieldBracketsSuffix;
	}

	public String getFieldId() {
		return fieldId;
	}

	public void setFieldId(String fieldId) {
		this.fieldId = fieldId;
	}

	public ArrayList getLFieldValues() {
		return lFieldValues;
	}

	public void setLFieldValues(ArrayList lFieldValues) {
		this.lFieldValues = lFieldValues;
	}

	/**
	 * Gets the unique value of the criteria for unitary operators.
	 * 
	 * @return Object, value for the criteria.
	 */
	public Object getFieldValue() {
		// If there is only one value then return this value
		if (this.lFieldValues != null && this.lFieldValues.size() == 1)
			return this.lFieldValues.get(0);
		// In other case (no values or more than one) return null
		else
			return null;
	}

	/**
	 * Sets the LFieldValues with an unique value for unitary operators.
	 * 
	 * @param value
	 *            Object, value for the criteria.
	 */
	public void setFieldValue(Object value) {
		this.lFieldValues = new ArrayList();
		this.lFieldValues.add(value);
	}

	public String getFieldOperator() {
		return fieldOperator;
	}

	public void setFieldOperator(String fieldOperator) {
		this.fieldOperator = fieldOperator;
	}

	public String getFieldLogicOperator() {
		return fieldLogicOperator;
	}

	public void setFieldLogicOperator(String fieldLogicOperator) {
		this.fieldLogicOperator = fieldLogicOperator;
	}

	public String getFieldBracketsPrefix() {
		return fieldBracketsPrefix;
	}

	public void setFieldBracketsPrefix(String fieldBracketsPrefix) {
		this.fieldBracketsPrefix = fieldBracketsPrefix;
	}

	public String getFieldBracketsSuffix() {
		return fieldBracketsSuffix;
	}

	public void setFieldBracketsSuffix(String fieldBracketsSuffix) {
		this.fieldBracketsSuffix = fieldBracketsSuffix;
	}

	public boolean isFieldNormalized() {
		return fieldNormalized;
	}

	public void setFieldNormalized(boolean fieldNormalized) {
		this.fieldNormalized = fieldNormalized;
	}

	/**
	 * Returns the SQL WHERE statement string value corresponding to the
	 * Criteria.
	 * 
	 * @param isFirstCriteria
	 *            boolean, True in case the criteria is the first of the WHERE
	 *            statement.
	 * @return String, sql string value.
	 */
	public String getCriteriaAsSql(boolean isFirstCriteria) {
		String result = "";

		// Add the logic operator to append the criteria.
		if (this.getFieldLogicOperator() != null
				&& this.getFieldLogicOperator().length() > 0
				&& !isFirstCriteria) {
			result += " " + this.getFieldLogicOperator();
		}

		// Add the brackets before the criteria.
		if (this.getFieldBracketsPrefix() != null
				&& this.getFieldBracketsPrefix().length() > 0) {
			result += " " + this.getFieldBracketsPrefix();
		}

		// Add the criteria database field name.
		if (this.getFieldId() != null && this.getFieldId().length() > 0) {
			if (this.isFieldNormalized())
				result += " " + InterfaceDB.NORMALIZATION_FUNCTION + "("
						+ this.getFieldId() + ")";
			else
				result += " " + this.getFieldId();
		}

		// Add criteria operator.
		if (this.getFieldOperator() != null
				&& this.getFieldOperator().length() > 0) {
			result += " " + this.getFieldOperator();
			// If the operator == IN a opening bracket must be added.
			if (this.getFieldOperator().equals(InterfaceDB.SQL_OPERATOR_IN))
				result += " (";
		}

		// Add the criteria values. We'll use SQL statements so we must add a ?
		// character
		// for each one of the criteria values. Next we'll use the right
		// statement.setObject()
		// as necessary.
		for (int i = 0; this.getLFieldValues() != null
				&& i < this.getLFieldValues().size(); i++) {
			if (this.isFieldNormalized()) {
				String val = (String) this.getLFieldValues().get(i);
				result += " " + InterfaceDB.NORMALIZATION_FUNCTION + "('%"
						+ val + "%')";
			} else
				result += " ?";

			// If we have more than a single value then value delimiters must be
			// added
			if (i < this.getLFieldValues().size() - 1) {
				// If the operator is BETWEEN then the delimiter is "AND"
				if (this.getFieldOperator().equals(
						InterfaceDB.SQL_OPERATOR_BETWEEN))
					result += " " + InterfaceDB.LOGIC_OPERATOR_AND + " ";
				// If the operator is not BETWEEN then the delimiter is ","
				else
					result += ", ";
			} // if separators must be added (more than a single value)
		} // for

		// If the operator == IN we must close the opened bracket
		if (this.getFieldOperator().equals(InterfaceDB.SQL_OPERATOR_IN))
			result += ") ";

		// Add the brackets after the where criteria
		if (this.getFieldBracketsSuffix() != null
				&& this.getFieldBracketsSuffix().length() > 0) {
			result += " " + this.getFieldBracketsSuffix();
		}

		return result;
	}

	/**
	 * Builds an Hibernate Criterion instance with the SQL WHERE statement
	 * corresponding to the search criteria.
	 * 
	 * @return Criterion, Hibernate Criterion built for the searching criteria.
	 */
	public Criterion getCriteria() {
		Criterion result = null;

		// Add criteria operator.
		if (InterfaceDB.SQL_OPERATOR_EQUAL.equals(this.getFieldOperator())) {
			Object val = this.getFieldValue();
			if (val != null) {
				result = Restrictions.eq(this.getFieldId(), val);
			} else
				result = null;
		} else if (InterfaceDB.SQL_OPERATOR_GREATER.equals(this
				.getFieldOperator())) {
			Object val = this.getFieldValue();
			if (val != null) {
				result = Restrictions.gt(this.getFieldId(), val);
			} else
				result = null;
		} else if (InterfaceDB.SQL_OPERATOR_GREATER_EQUAL.equals(this
				.getFieldOperator())) {
			Object val = this.getFieldValue();
			if (val != null) {
				result = Restrictions.ge(this.getFieldId(), val);
			} else
				result = null;
		} else if (InterfaceDB.SQL_OPERATOR_LOWER.equals(this
				.getFieldOperator())) {
			Object val = this.getFieldValue();
			if (val != null) {
				result = Restrictions.lt(this.getFieldId(), val);
			} else
				result = null;
		} else if (InterfaceDB.SQL_OPERATOR_LOWER_EQUAL.equals(this
				.getFieldOperator())) {
			Object val = this.getFieldValue();
			if (val != null) {
				result = Restrictions.le(this.getFieldId(), val);
			} else
				result = null;
		} else if (InterfaceDB.SQL_OPERATOR_LIKE
				.equals(this.getFieldOperator())) {
			Object val = this.getFieldValue();
			if (val != null) {
				result = Restrictions.like(this.getFieldId(), val);
			} else
				result = null;
		} else if (InterfaceDB.SQL_OPERATOR_BETWEEN.equals(this
				.getFieldOperator())) {
			if (this.getLFieldValues() != null
					&& this.getLFieldValues().size() == 2) {
				Object val1 = this.getLFieldValues().get(0);
				Object val2 = this.getLFieldValues().get(1);
				result = Restrictions.between(this.getFieldId(), val1, val2);
			} else
				result = null;
		} else if (InterfaceDB.SQL_OPERATOR_NOT_EQUAL.equals(this
				.getFieldOperator())) {
			Object val = this.getFieldValue();
			if (val != null) {
				result = Restrictions.ne(this.getFieldId(), val);
			} else
				result = null;
		} else if (InterfaceDB.SQL_OPERATOR_IN.equals(this.getFieldOperator())) {
			if (this.getLFieldValues() != null
					&& this.getLFieldValues().size() > 0) {
				result = Restrictions.in(this.getFieldId(), this
						.getLFieldValues().toArray());
			} else
				result = null;
		}

		else if (InterfaceDB.SQL_BOOLEAN_FUNCTION_IS_NULL.equals(this
				.getFieldOperator()))
			result = Restrictions.isNull(this.getFieldId());
		else if (InterfaceDB.SQL_BOOLEAN_FUNCTION_IS_NOT_NULL.equals(this
				.getFieldOperator()))
			result = Restrictions.isNotNull(this.getFieldId());

		return result;
	}

	/**
	 * Returns the SQL ORDER BY statement string value corresponding to the
	 * Criteria.
	 * 
	 * @param isFirstCriteria
	 *            boolean, True in case the criteria is the first of the ORDER
	 *            BY statement.
	 * @return String, sql string value.
	 */
	public String getOrderCriteriaAsSql(boolean isFirstCriteria) {
		String result = "";

		// Add the logic operator to append the order by criteria
		if (this.getFieldLogicOperator() != null
				&& this.getFieldLogicOperator().length() > 0
				&& !isFirstCriteria) {
			result += " " + this.getFieldLogicOperator();
		}

		// Add the field name of the order by criteria
		if (this.getFieldId() != null && this.getFieldId().length() > 0) {
			result += " " + this.getFieldId();
		}

		// Add the order by criteria values (ASC || DESC)
		String val = "";
		try {
			val = (String) this.getLFieldValues().get(0);
		} catch (Exception ex) {
			val = "";
		}
		result += " " + val;

		return result;
	}

	/**
	 * Returns tag Id assigned to CVS source file.
	 */
	public static String getRevision() {
		return "$Id: CriteriaBean.java,v 1.3 2009-06-18 10:38:11 juancarlos Exp $";
	}
}
